-- @owner: opentestcase033
-- @date: 2023-06-07
-- @testpoint: 通过全文搜索加上打印函数方式绕过列权限，合理报错

--step1:创建测试用户测试表;expect:成功
create user u_privilege_0011 with password "Schina1234@";
create table t_privilege_0011(id int, body text, title text, last_mod_date date);
insert into t_privilege_0011 values(1, 'China, officially the People''s Republic of China(PRC), located in Asia, is the world''s most populous state.', 'China', '2010-1-1'),
(2, 'America is a rock band, formed in England in 1970 by multi-instrumentalists Dewey Bunnell, Dan Peek, and Gerry Beckley.', 'America', '2010-1-1'),
(3, 'England is a country that is part of the United Kingdom. It shares land borders with Scotland to the north and Wales to the west.', 'England','2010-1-1');

--step2:授权select列权限;expect:成功
grant select (id) on t_privilege_0011 to u_privilege_0011;

--step3:利用全文搜索功能加输出函数尝试绕过列权控;expect:越权操作失败
set role u_privilege_0011 password "Schina1234@";
create or replace function raise_demo(id integer,name text) returns text language plpgsql immutable as $$
begin 
raise notice 'id :% , name : %',id,name;
return name;
end;
$$;
/

select id from t_privilege_0011 where to_tsvector(raise_demo(id, body)) @@ to_tsquery('america');
select id from t_privilege_0011 where to_tsvector(body) @@ to_tsquery(raise_demo(id, body));

--step4:清理环境;expect:成功
reset role;
drop table t_privilege_0011 cascade;
drop user u_privilege_0011 cascade;
